insert into  asm_letture_x_sic_1
select vc.con_anno,vc.con_id,ubi.cuf_id,
to_number(substr(ATT.ATT_IDEXT,7,7)) CODICE_ALLACCIAMENTO,
substr(ATT.ATT_IDEXT,-1,1)  progressivo_utenza,ATT.ATT_IDEXT,
(select distinct decode (max(ver_dtfine),null,to_date('01012013','ddmmyyyy'),max(ver_dtfine)) 
                    from sic4u_ambuet.V_lettura le1 where le1.cuf_id=ubi.cuf_id  and le1.az_id='02')  data_let
                    




from
sic4u_ambuet.v_CONTRATTO VC,
sic4u_ambuet.CONUBIFRN  UBI, sic4u_ambuet.ATTACCO ATT


where 
vc.con_anno=ubi.con_anno
and vc.con_id=ubi.con_id

and (VC.gf_id =  ^Gruppo Bollettazione Sic xx;N;NUMERICO^^
        OR VC.gf_id =  ^Gruppo Bollettazione Sic yy;N;NUMERICO^^
             OR VC.gf_id =  ^Gruppo Bollettazione Sic kk;N;NUMERICO^^)
and ubi.ver_dtfine = (select MAX(VER_dtfine) from sic4u_ambuet.CONUBIFRN ubi1 where ubi.att_id=ubi1.att_id and ubi1.az_id='02')
and vc.ver_dtfine > sysdate-90
and ubi.az_id=02
and vc.az_id=02
and ubi.ser_id=03
AND UBI.ATT_ID=ATT.ATT_ID
AND ATT.AZ_ID=02
AND (SELECT CASE
                   WHEN ista.iss_staid <> 0
                   AND ista.iss_staidsuper = 0
                   AND ista.iss_sstaidsuper = 0
                      THEN ista.iss_staid
                   WHEN ista.iss_staid <> 0
                   AND ista.iss_staidsuper <> 0
                   AND ista.iss_sstaidsuper = 0
                      THEN ista.iss_staidsuper
                   WHEN ista.iss_staid <> 0
                   AND ista.iss_staidsuper <> 0
                   AND ista.iss_sstaidsuper <> 0
                      THEN ista.iss_sstaidsuper
                   ELSE -1
                END
           FROM sic4u_ambueT.ista ista
          WHERE ista.az_id = UBI.az_id
            AND ista.csi_id = UBI.csi_id
            AND ista.ista_verstato = UBI.ista_verstato) NOT IN (3,1)
AND vc.sta_idsys NOT IN (3,1)